using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Dynamic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;
using MySqlConnector;
using RFS.BizLogic.Sys.impl;
using RFS.BizLogic.utils;
using RFS.Domain.PaiShi;
using RFS.Domain.PaiShi.Accounting;
using RFS.Domain.Sys;
using ycx;

namespace RFS.BizLogic.PaiShi.Ports.Adapters
{
    public class AccRepoMySqlAdapter : IRepoAcc
    {
        private string _connectionString = "";

        private const string EntityTableName = "acc_tbl_AccSetEntityGraph";
        private const string DicTableName = "acc_tbl_AccSetDic";
        private const string AvsEntityListTableName = "acc_tbl_Avs_Entity_List";


        #region TALBES

        void TryToCrateTables()
        {
            foreach (var kv in _tables2Creeate)
            {
                ExecuteSqlQuery(kv.Value);
            }

            _config.ConfigSys.AccRepoInitedOK = true;
        }


        Dictionary<string, string> _tables2Creeate = new Dictionary<string, string>()
        {
            {
                AvsEntityListTableName,
                $@"
 CREATE TABLE IF NOT EXISTS {AvsEntityListTableName} (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    BeginAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    EndAt DATETIME DEFAULT CURRENT_TIMESTAMP,
   BalanceAmountQ DECIMAL(19, 4),
   BalanceAmountC DECIMAL(19, 4),
   DebitAmountQ DECIMAL(19, 4),
   DebitAmountC DECIMAL(19, 4),
   CreditAmoutQ DECIMAL(19, 4),
   CreditAmoutC DECIMAL(19, 4)
);

"
            },

            {
                EntityTableName,
                $@"
 CREATE TABLE IF NOT EXISTS {EntityTableName} (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entityJson LONGTEXT
);

"
            },


            {
                DicTableName, $@"
CREATE TABLE IF NOT EXISTS {DicTableName} (
                                                  
                                                 AvId BIGINT UNSIGNED,
                                                 AvsId BIGINT UNSIGNED,
                                                 AccItemId BIGINT  UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                                                 BizOrderId BIGINT UNSIGNED,
                                                 BizOrderType INT UNSIGNED,
                                                 LocalId BIGINT UNSIGNED,

                                                 IsChecked BOOLEAN DEFAULT 0,
                                                 beginAt DATETIME DEFAULT CURRENT_TIMESTAMP,
                                                 EndAt DATETIME DEFAULT CURRENT_TIMESTAMP,

                                                 INDEX idx_AvId (AvId),
                                                 INDEX idx_AvsId (AvsId),
                                                 INDEX idx_AccItemId (AccItemId),
                                                 INDEX idx_LocalId (LocalId),
                                                 INDEX idx_BizOrderId (BizOrderId),
                                                 INDEX idx_beginAt (beginAt),
                                                 INDEX idx_EndAt (EndAt)
); 
"
            },
        };

        #endregion

        private ILog _log;
        private IConfigT _config;

        public AccRepoMySqlAdapter(ILog log, IConfigT config)
        {
            _log = log;
            _config = config;
            // _config = config;
            string connectionStringName = "CnnStrAccDb";
            _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;


            Task.Run(() =>
            {
                if (!_config.ConfigSys.AccRepoInitedOK)
                {
                    TryToCrateTables();
                }
            });
        }

        public List<dynamic> ExecuteSqlQuery(string query)
        {
            List<dynamic> results = new List<dynamic>();


            using (MySqlConnection connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dynamic obj = new ExpandoObject();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                var fieldName = reader.GetName(i);
                                var fieldValue = reader[i];
                                ((IDictionary<string, object>)obj).Add(fieldName, fieldValue);
                            }

                            results.Add(obj);
                        }
                    }
                }
            }

            return results;
        }

        public void DeleteEntity(AVSEntity o)
        {
            var dic = o.ToAccItemAndOrderBasedDicItem();

            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (var tran = connection.BeginTransaction())
                {
                    #region de entity

                    var sql1 = $"DELETE FROM {EntityTableName} WHERE ID = {o.Id}";
                    using (var command = new MySqlCommand(sql1, connection, tran))
                    {
                        command.ExecuteNonQuery();
                    }

                    #endregion

                    #region de dic for search

                    if (dic.Count() > 0)
                    {
                        var sql4 =
                            $"delete from  {DicTableName} where AccItemId in ({dic.Select(x => x.AccItemId).JoinToString()})";
                        using (var command = new MySqlCommand(sql4, connection, tran))
                        {
                            command.ExecuteNonQuery();
                        }
                    }

                    #endregion

                    tran.Commit();
                }

                connection.Close();
            }
        }

        public AVSEntity GetById(ulong id)
        {
            var sql = $"select id,entityJson FROM {EntityTableName} WHERE ID = {id}";
            var obj = ExecuteSqlQuery(sql);
            if (obj.Count == 0) throw new Exception($"找不到记录【{id}】。");

            var json = $"{obj[0].entityJson}";
            var en = json.ToObject<AVSEntity>();
            return en;
        }

        public IEnumerable<AVSListItemEntity> GetTheLastestRecords(int days = 7)
        {
            var date = DateTime.Today.AddDays(-days);
            var sql = $"select *  FROM {AvsEntityListTableName} where EndAt>='{date:yyyy-MM-dd}' ORDER BY  EndAt desc  ";
            var dobj = ExecuteSqlQuery(sql);
            var res = dobj.Select(x => new AVSListItemEntity()
            {
                Id = x.id,
                AccPeriod = new AccountPeriod(beginDate: (DateTime)x.BeginAt, endDate:  (DateTime)x.EndAt),
                TotalBalance = new Amount(amount:  x.BalanceAmountC, quantity:  x.BalanceAmountQ),
                DebitAmount = new Amount(amount:(decimal) x.DebitAmountC, quantity: (decimal)x.DebitAmountQ),
                CreditAmout = new Amount(amount: (decimal)x.CreditAmoutC, quantity: (decimal)x.CreditAmoutQ),
            }).ToList();

            return res;
        }

        public void SaveTheAvsListItem(IDayBasedWorkFlowNodeAccountingVoucherSetWrapper o)
        {
            var entity = o.ToListItem();

            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (var tran = connection.BeginTransaction())
                {
                    var sql1 = $"DELETE FROM {AvsEntityListTableName} WHERE ID = {o.Id}";
                    using (var command = new MySqlCommand(sql1, connection, tran))
                    {
                        command.ExecuteNonQuery();
                    }


                    string sql3 = $@"
                        INSERT INTO {AvsEntityListTableName} (id,EndAt,BeginAt,BalanceAmountQ,BalanceAmountC,DebitAmountQ,DebitAmountC,CreditAmoutQ,CreditAmoutC )
                                        VALUES (@Id, @EndAt, @BeginAt, @BalanceAmountQ, @BalanceAmountC, @DebitAmountQ, @DebitAmountC, @CreditAmoutQ, @CreditAmoutC );
                    ";

                    using (var command = new MySqlCommand(sql3, connection, tran))
                    {
                        command.Parameters.AddWithValue("@Id", entity.Id);
                        command.Parameters.AddWithValue("@EndAt", entity.AccPeriod.EndDate);
                        command.Parameters.AddWithValue("@BeginAt", entity.AccPeriod.BeginDate);
                        command.Parameters.AddWithValue("@BalanceAmountQ", entity.TotalBalance.Quantity);
                        command.Parameters.AddWithValue("@BalanceAmountC", entity.TotalBalance.AmountCash);
                        command.Parameters.AddWithValue("@DebitAmountQ", entity.DebitAmount.Quantity);
                        command.Parameters.AddWithValue("@DebitAmountC", entity.DebitAmount.AmountCash);
                        command.Parameters.AddWithValue("@CreditAmoutQ", entity.CreditAmout.Quantity);
                        command.Parameters.AddWithValue("@CreditAmoutC", entity.CreditAmout.AmountCash);
                        // command.Parameters.AddWithValue("@IsChecked", entity.IsChecked);
                        command.ExecuteNonQuery();
                    }


                    tran.Commit();
                    connection.Close();
                }
            }
        }


        public void SaveEntity(AVSEntity o)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                using (var tran = connection.BeginTransaction())
                {
                    #region saving entity

                    var sql1 = $"DELETE FROM {EntityTableName} WHERE ID = {o.Id}";
                    using (var command = new MySqlCommand(sql1, connection, tran))
                    {
                        command.ExecuteNonQuery();
                    }


                    string sql = $@"
            INSERT INTO {EntityTableName} (id, entityJson)
            VALUES (@id, @entityJson);
        ";
                    using (var command = new MySqlCommand(sql, connection, tran))
                    {
                        command.Parameters.AddWithValue("@id", o.Id);
                        command.Parameters.AddWithValue("@entityJson", o.ToJson());
                        command.ExecuteNonQuery();
                    }

                    #endregion

                    #region saving dic for search

                    var dic = o.ToAccItemAndOrderBasedDicItem();
                    if (dic.Count() > 0)
                    {
                        var sql4 =
                            $"delete from  {DicTableName} where AvsId={o.Id} ";
                        using (var command = new MySqlCommand(sql4, connection, tran))
                        {
                            command.ExecuteNonQuery();
                        }

                        foreach (var entity in dic)
                        {
                            string sql3 = $@"
                        INSERT INTO {DicTableName} (BeginAt,EndAt,AvId,AvsId,AccItemId,LocalId,BizOrderId,BizOrderType,IsChecked )
                                        VALUES (@BeginAt, @EndAt, @AvId, @AvsId, @AccItemId, @LocalId, @BizOrderId, @BizOrderType, @IsChecked );
                    ";

                            using (var command = new MySqlCommand(sql3, connection, tran))
                            {
                                command.Parameters.AddWithValue("@BeginAt", entity.BeginAt);
                                command.Parameters.AddWithValue("@EndAt", entity.EndAt);
                                command.Parameters.AddWithValue("@AvId", entity.AvId);
                                command.Parameters.AddWithValue("@AvsId", entity.AVSId);
                                command.Parameters.AddWithValue("@AccItemId", entity.AccItemId);
                                command.Parameters.AddWithValue("@LocalId", entity.LocalId);
                                command.Parameters.AddWithValue("@BizOrderId", entity.BizOrderId);
                                command.Parameters.AddWithValue("@BizOrderType", entity.BizOrderType);
                                command.Parameters.AddWithValue("@IsChecked", entity.IsChecked);
                                command.ExecuteNonQuery();
                            }
                        }
                    }

                    #endregion


                    tran.Commit();
                    connection.Close();
                }
            }
        }




    }
}